<?php
/**
 * Die Klasse, um die Datenbankfunktionen
 * zu verwalten bzw. um auf die Datenbank zuzugreifen.
 *
 * @author Timo
 */
class Database
{
    private $server, $name, $user, $password;
    private $instance = null; // Datenbankinstanz

/*
 * Datenbank wechseln..
 */
public function ChangeDatabase($database)
{
    $path = dirname(__FILE__);
    $settings = parse_ini_file($path . '/config_ini.php', True);

    if(strtolower($database) == 'joomla')
    {
        $this->server   = $settings['Joomla']['server'];
        $this->name     = $settings['Joomla']['name'];
        $this->user     = $settings['Joomla']['user'];
        $this->password = $settings['Joomla']['password'];
    }
    else if(strtolower($database) == 'ranking')
    {
        $this->server   = $settings['Ranking']['server'];
        $this->name     = $settings['Ranking']['name'];
        $this->user     = $settings['Ranking']['user'];
        $this->password = $settings['Ranking']['password'];
    }
    else
    {
        echo 'Unknown database.<br />';
    }
}

/**
 * Daten werden gesetzt.
 *
 * @author Timo
 */
    function __construct()
    {
        $this->ChangeDatabase('ranking');
    }

/**
 * Eventuell offene Verbindung schließen.
 *
 * @author Timo
 */
   function __destruct()
   {
       $this->Disconnect();
   }

/*
 * Verbindung aufbauen..
 * 
 * @author Timo
 */
public function Connect()
{
    if($this->instance == null)
    {
        try
        {
            $this->instance = new MySQLi($this->server, $this->user, $this->password, $this->name);
        }
        catch (Exception $ex)
        {
            echo 'Could not establish a database connection<br />' . $ex;
        }
    }
}

/*
 * Verbindung schließen..
 *
 * @author Timo
 */
public function Disconnect()
{
    if($this->instance != null)
    {
        $this->instance->close();
        $this->instance = null;
    }
}

/**
 * SELECT Statement mit optionalem WHERE Statement.
 *
 * Bsp. SELECT * FROM [Tabelle] WHERE [Statement]
 *
 * Gibt Objekt bzw. Objekte zurück.
 *
 * @author Timo
 */
    public function Select($table, $where = '', $orderby = '', $order = 'ASC')
    {
        $this->Connect();

        $sql  = 'SELECT * FROM `' . $table. '`';

        if($where != '')
        {
            $sql .= ' WHERE ' . $where;
        }
        if($orderby != '')
        {
            switch (strtoupper($order))
            {
                case 'ASC': $sql .= ' ORDER BY `' . $orderby . '` ASC';
                            break;
                case 'DESC': $sql .= ' ORDER BY `' . $orderby . '` DESC';
                            break;
                default: $sql .= ' ORDER BY `' . $orderby . '` ASC';
                            break;
            }
        }
        
        try
        {
            $output = $this->instance->query($sql);
            if(!$output instanceof mysqli_result)
            {
                return false; // Kein gültiges Resultset
            }
            $result = array();
            while($data = $output->fetch_object())
            {
                $result[] = $data;
            }
        }
        catch (Exception $ex)
        {
          echo $ex;
        }
        return $result;
    }

/**
 * SELECT Statement mit optionalem WHERE Statement.
 *
 * Bsp. SELECT * FROM [Tabelle] WHERE [Statement]
 *
 * Gibt Objekt bzw. Objekte zurück.
 *
 * Unterschied zu normalem Select -> Auswählen von gewissen Spalten..
 *
 * @author Timo
 */

    public function SelectColumns($table, $columns, $where = '', $orderby = '', $order = 'ASC')
    {
        $this->Connect();

        $sql  = 'SELECT ';
        $sql .= $columns;
        $sql .= ' FROM `' . $table . '`';
        if($where != "")
        {
            $sql .= ' WHERE ' . $where;
        }
        if($orderby != '')
        {
            switch (strtoupper($order))
            {
                case 'ASC': $sql .= ' ORDER BY `' . $orderby . '` ASC';
                            break;
                case 'DESC': $sql .= ' ORDER BY `' . $orderby . '` DESC';
                            break;
                default: $sql .= ' ORDER BY `' . $orderby . '` ASC';
                            break;
            }
        }

        try
        {
            $output   = $this->instance->query($sql);
            if(!$output instanceof mysqli_result)
            {
                return false; // Kein gültiges Resultset
            }
            $result   = array();
            while($data = $output->fetch_object())
            {
                $result[] = $data;
            }
        }
        catch (Exception $ex)
        {
          echo $ex;
        }

        return $result;
    }

   /*
    * Direktes Ausführen eines SQL Statements.
    */
   public function ExecSQL($sql_string)
   {
        $result = null;
        if ($sql_string != '')
        {
            if ($this->instance == null)
            {
                $this->connect();
            }
            try
            {
                $output = $this->instance->query($sql_string, MYSQLI_USE_RESULT);
                if(!$output instanceof mysqli_result)
                {
                    $result = false; // Kein gültiges Resultset.
                }
                else
                {
                    $result = array();
                    while($data = $output->fetch_object())
                    {
                        $result[] = $data;
                    }
                }
            }
            catch(Exception $ex)
            {
                echo $ex;
            }
        }
        return $result;
   }

/**
 * Durch INSERT Statement Objekt in die Datenbank schreiben.
 * Wichtig: Die beiden übergebenen Arrays müssen gleichgroß sein!
 *
 * @author Timo
 */
    public function Insert($table, $columnArray, $valueArray)
    {
        // Die Arrays für die Spalten und die Datentypen müssen gleichgroß sein.
        if(sizeof($columnArray) != sizeof($valueArray))
        {
            echo 'Different array size!';
            return false;
        }

        $this->Connect();

        $sql    = 'INSERT INTO `' . $table . '` (';
        $values = 'VALUES(';
        $i      = 0;
        foreach($columnArray as $column)
        {
            $sql    .= $column;
            $values .= '"' . $valueArray[$i] . '"';
            $i++;
            if($i < sizeof($columnArray))
            {
                $sql    .= ', ';
                $values .= ', ';
            }
        }
        $sql    .= ')';
        $values .= ')';
        $sql    .= ' ' . $values;

        try
        {
            $typeString = '';
            foreach($valueArray as $value)
            {
                switch (gettype($value))
                {
                    case 'integer': $typeString .= 'i';
                                    break;
                    default: $typeString .= 's'; //Default => String
                                    break;
                }
            }
        }
        catch (Exception $ex)
        {
          echo $ex;
          return false;
        }

            try
            {
                $this->ExecSQL($sql);
            }
            catch(Exception $ex)
            {
                echo 'Error in SQL statement.<br />---------------------<br />';
                echo '<br />-------Exception---------<br />';
                echo $ex;
                return false;
            }
        return true;
    }

/**
 * Durch UPDATE Statement Objekt in der Datenbank aktualisieren.
 * Optional: WHERE Statement, bspw. "WHERE Points = 100"
 *
 * @author Timo
 */
    public function Update($table, $column, $value, $where = "")
    {
        $this->Connect();

        $sql = 'UPDATE ' . $table . ' SET ' . $column . ' = ' . $value;
        if($where != '')
        {
            $sql .= ' ' . $where;
        }
        try
        {
            $this->instance->query($sql);
        }
        catch (Exception $ex)
        {
          echo $ex;
        }
    }

    /**
 * Tabelle in der Datenbank erstellen.
  * Values: 'i' = integer, 's' = string, 'auto' = auto_increment
 * Wichtig: Die beiden übergebenen Arrays müssen gleichgroß sein!
  *         'auto' darf nur einmal vergeben werden!
 *
 * @author Timo
 */
    public function CreateTable($table, $columnArray, $valueArray)
    {
        // Die Arrays für die Spalten und die Datentypen müssen gleichgroß sein.
        if(sizeof($columnArray) != sizeof($valueArray))
        {
            echo 'Different array size!<br />';
            return false;
        }

        $this->Connect();

        $sql = 'CREATE TABLE IF NOT EXISTS `' . $table . '` (';

        try
        {
            $i          = 0;
            $auto_count = 0;
            foreach($columnArray as $column)
            {
                switch($valueArray[$i])
                {
                    case 's': $value = 'VARCHAR(255)';
                                break;

                    case 'i': $value = 'INT';
                                break;
                    case 'bool': $value = 'TINYINT(1)';
                                    break;

                    case 'date': $value = 'date';
                                    break;

                    case 'auto': $value = 'INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL';
                                 $auto_count++;
                                    break;

                    default: $value = 'VARCHAR(255)'; // Varchar als Standardwert, weil dort ziemlich alles reinpasst.
                             echo '<br />Invalid argument. ' . $column . ' will be casted to varchar(255).<br />';
                                        break;
                }

                if($auto_count > 1)
                {
                    echo 'Only one "auto" argument is allowed.<br />';
                    return false;
                }

                $sql .= '`' . $column . '` ' . $value;
                $i++;
                if($i < sizeof($columnArray))
                {
                    $sql .= ', ';
                }
            }
            $sql .= ')';
        }
        catch (Exception $ex)
        {
          echo $ex;
          return false;
        }

        if($this->instance->query($sql))
        {
            echo '<br />' . $table . ' successfully created.<br />';
            $result = TRUE;
        }
        else
        {
            echo '<br />Could not execute SQL query: ' . $sql . '<br />';
            $result = FALSE;
        }
        return $result;
    }

/**
 * Tabellen einer Serie löschen.
 *
 * @author Timo
 */
    public function DeleteTables($prefix)
    {
        echo '<b>Deleting database tables...</b><br />';

        $driver_db   = $prefix . '_drivers';
        $team_db     = $prefix . '_teams';
        $vehicle_db  = $prefix . '_vehicles';
        $track_db    = $prefix . '_tracks';
        $result_db   = $prefix . '_results';
        $calendar_db = $prefix . '_calendar';
        $picture_db  = $prefix . '_pictures';

        try
        {
            $this->Connect();
            $this->DeleteTable($driver_db);
            $this->DeleteTable($team_db);
            $this->DeleteTable($vehicle_db);
            $this->DeleteTable($track_db);
            $this->DeleteTable($result_db);
            $this->DeleteTable($calendar_db);
            $this->DeleteTable($picture_db);
        }
        catch(Exception $ex)
        {
            echo $ex;
            $this->Disconnect();
            return false;
        }
        return true;
    }

/**
 * Tabellen für eine Serie erstellen.
 *
 * //TODO// -> Dynamischer
 *
 * @author Timo
 */
    public function GenerateTables($series_table, $prefix, $league_id, $steam = false)
    {
        echo '<b>Generating database tables...</b><br />';

        $columns = array('ID_Series' , 'League_ID', 'Name', 'Shortcut', 'Maximum_drivers', 'Maximum_drivers_per_team', 'Season', 'Mulligan', 'Steam');
        $values  = array('auto'      , 'i'        , 's'   , 's'       , 'i'              , 'i'                       , 'i'     , 'bool'    , 'bool');

        $driver_db   = $prefix . '_drivers';
        $team_db     = $prefix . '_teams';
        $vehicle_db  = $prefix . '_vehicles';
        $track_db    = $prefix . '_tracks';
        $result_db   = $prefix . '_results';
        $calendar_db = $prefix . '_calendar';
        $picture_db  = $prefix . '_pictures';

        $driver_columns = array('ID_Driver', 'JID', 'Name', 'Number' , 'Team_ID', 'Vehicle_ID', 'Points', 'Races', 'Poles', 'Wins', 'Top5', 'Penalty_Points', 'Active'); // ID_Driver wird automatisch gefüllt..
        $driver_values  = array('auto'     , 'i'  , 's'   , 'i'      , 'i'      , 'i'         , 'i'     , 'i'    , 'i'    , 'i'   , 'i'   , 'i'             , 'bool');

        if($steam)
        {
            $driver_columns[] = 'Steamname';
            $driver_values[]  = 's';
        }

        $team_columns = array('ID_Team', 'JID', 'Name', 'Managers', 'Password', 'Series'); // ID_Team wird automatisch gefüllt..
        $team_values  = array('auto'   , 'i'  , 's'   , 's'       , 's'       , 's');

        $vehicle_columns = array('ID_Vehicle', 'Name', 'Engine', 'Engine_Power', 'RPM'); // ID_Vehicle wird automatisch gefüllt..
        $vehicle_values  = array('auto'      , 's'   , 's'     , 'i'           , 'i');

        $track_columns = array('ID_Track', 'Name', 'Length', 'Laps', 'Location', 'Last_Winner', 'Hotlap', 'Hotlap_Owner'); // ID_Track wird automatisch gefüllt..
        $track_values  = array('auto'     , 's'  , 'i'     , 'i'   , 's'       , 'i'          , 'i'     , 'i');

        /*
         * Ergebnisse werden in Dateien gespeichert
         */
        $result_columns = array('ID_Result', 'File', 'Season', 'RaceNo', 'Track_ID'); // ID_Result wird automatisch gefüllt..
        $result_values  = array('auto'     , 's'   , 'i'     , 'i'     , 'i');

        /*
         * Tabelle für den Rennkalender
         */
        $calendar_columns = array('ID_Race', 'Track_ID', 'Start_Date', 'Finished', 'Picture_ID');
        $calendar_values  = array('auto'   , 'i'       , 'date'      , 'bool'    , 'i');

        $picture_columns = array('ID_Picture', 'Name', 'URL', 'Description');
        $picture_values  = array('auto'      , 's'   , 's'  , 's');

        $admin_columns = array('ID_Admin', 'Name', 'JID', 'Series', 'Leagues', 'Super_Admin');
        $admin_values  = array('auto'    , 's'   , 'i'  , 's'     , 's'      , 'bool');

        try
        {
            $this->Connect();
            $this->CreateTable('Series', $columns, $values);
            $this->CreateTable('Admins', $admin_columns, $admin_values);

            $this->CreateTable($driver_db, $driver_columns, $driver_values);
            $this->CreateTable($team_db, $team_columns, $team_values);
            $this->CreateTable($vehicle_db, $vehicle_columns, $vehicle_values);
            $this->CreateTable($track_db, $track_columns, $track_values);
            $this->CreateTable($result_db, $result_columns, $result_values);
            $this->CreateTable($calendar_db, $calendar_columns, $calendar_values);
            $this->CreateTable($picture_db, $picture_columns, $picture_values);
        }
        catch(Exception $ex)
        {
            echo $ex;
            $this->Disconnect();
            return false;
        }
        $this->Disconnect();
        return true;
    }

   /*
    * Element aus der Datenbank löschen.
    */
    public function Delete($table, $where)
    {
        $success = $this->ExecSQL('DELETE FROM ' . $table . ' WHERE ' . $where);
        return $success;
    }

    /*
    * Datenbanktabelle löschen.
    */
    public function DeleteTable($table)
    {
        $success = $this->ExecSQL('DROP TABLE ' . $table);
        return $success;
    }
}
?>